package DataBase;

import BaseClass.OrderInfo;
import BaseClass.PetInfo;
import BaseClass.UserInfo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class DataBase{
    private static Connection connection;

    public static void setConnection(){
        try {
                Class.forName("com.mysql.jdbc.Driver");
                connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/web", "gyx", "123456");
        }catch(ClassNotFoundException ex){
            System.out.print("classNotFound");
        }catch(SQLException ex){
            System.out.print("SQLException_Construct");
        }
    }

//用户信息判断、获取、生成
    //判断用户是否登录
    public static boolean JudgeByUsernameAndPassword(String username, String password) {
        setConnection();
        try {
            ResultSet resultSet = connection.prepareStatement("select * from user where username = "+ username +" and password = " + password).executeQuery();
            //存在
            if(resultSet.next()) return true;
            //不存在
        } catch (SQLException e) {
            e.printStackTrace();
        }
            return false;
    }
    //获取用户信息(判断密码正确后)
    public static UserInfo GetUserInfoByUsername(String username) {
        setConnection();
        UserInfo userInfo = new UserInfo();
        try {
            ResultSet resultSet = connection.prepareStatement("select * from user where username = "+ username).executeQuery();
            while (resultSet.next()){
                userInfo.setUsername(resultSet.getString(1));
                userInfo.setPassword(resultSet.getString(2));
                userInfo.setName(resultSet.getString(3));
                userInfo.setLocation(resultSet.getString(4));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return userInfo;
    }
    //修改用户信息
    public static void ChangeUserInfo(String username, String name ,String location) {
        setConnection();
        try {
            connection.prepareStatement("update user set name  = " + "\""+ name+"\""+" , location = "+ "\""+ location +"\""+" where username = " + "\""+ username+"\"").executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    //注册账号
    public static void SetNewUsernameAndPassword(String username, String password,String name) {
        setConnection();
        try {
            if(!HaveRegister(username))
                connection.prepareStatement("insert into user values(" + username+","+password+","+ name+","+null+")" ).executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //判断username是否存在
    public static boolean HaveRegister(String username) {
        setConnection();
        try {
            ResultSet resultSet = connection.prepareStatement("select * from user where username = "+ username).executeQuery();
            //已存在重复的username
            if(resultSet.next()) return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        //不存在,说明可以注册新账号
        return false;
    }

    //购物车物品获取、添加、删除
    //获取购物车中物品
    public static List<PetInfo> getCartInfo(String username) {
        setConnection();
        List<PetInfo> petInfo = new ArrayList<PetInfo>();
        try {
            ResultSet pet = connection.prepareStatement("select * from cart where username = " + username).executeQuery();
            while (pet.next()) {
                PetInfo p = new PetInfo();
                p.setPetClass(pet.getString(2));
                p.setPetName(pet.getString(3));
                p.setCost(pet.getDouble(4));
                p.setNum(pet.getInt(5));
                p.setPid(pet.getString(6));
                petInfo.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return petInfo;
    }
    //删除购物车物品
    public static void deleteCartInfo(String username, String pid){
        setConnection();
        try {
            connection.prepareStatement("delete from cart where username = "+ username +" and pid = " +"\""+ pid+"\"").executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //物品加入购物车中
    public static void addCartInfo(String username, PetInfo petInfo){
        setConnection();
        try {
            ResultSet resultSet = connection.prepareStatement("select num from cart where username = "+ username +" and pid = " + "\""+petInfo.getPid()+"\"").executeQuery();
            if (!resultSet.next()){//购物车中不存在该物品
                connection.prepareStatement("insert into cart values(" + username
                        +","+ "\""+petInfo.getPetClass() + "\""+","+ "\""+ petInfo.getPetName() + "\""+","+ "\""+ petInfo.getCost() + "\""+","+ "\""+ petInfo.getNum() + "\""+","+ "\""+ petInfo.getPid()
                        + "\""+")").executeUpdate();
            }
            else {//购物车中存在该产品
                int num = resultSet.getInt(1) + 1;
                connection.prepareStatement("update cart set num = " + num +" where username = " + "\""+ username + "\""+ " and " + "pid = "+ "\""+ petInfo.getPid()+ "\"").executeUpdate();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //更新购物车中信息
    public static void updateCartInfo(String username, String pid,int num){
        setConnection();
        try {
            ResultSet resultSet = connection.prepareStatement("select num from cart where username = "+ username +" and pid = " + "\""+pid+"\"").executeQuery();
            if (resultSet.next()){//购物车中存在该产品
                if(num <= 0){
                    connection.prepareStatement("delete from cart where username = "+ username +" and pid = " + "\""+pid+ "\"").executeUpdate();
                }
                else {
                    connection.prepareStatement("update cart set num = " + num + " where username = " + "\"" + username + "\"" + " and " + "pid = " + "\"" + pid + "\"").executeUpdate();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

//订单获取、添加、删除
    //获取订单
    public static List<OrderInfo> getOrderInfo(String username) {
        setConnection();
        List<OrderInfo> orderInfo = new ArrayList<OrderInfo>();
        try {
            ResultSet orders = connection.prepareStatement("select * from orders where username = " + username).executeQuery();
            while (orders.next()) {
                OrderInfo p = new OrderInfo();
                p.setID(orders.getString("orderID"));
                p.setProduct(orders.getString("orderProduct"));
                p.setCost(orders.getDouble("orderCost"));
                p.setStatus(orders.getString("orderStatus"));
                orderInfo.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return orderInfo;
    }
    //取消订单
    public static void deleteOrderInfo(String username, String orderID){
        setConnection();
            try {
            connection.prepareStatement("delete * from order where username = "+ username +" and orderID = " + orderID).executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //添加订单
    public static void addOrderInfo(String username){
        setConnection();

        String orderProduct = "";
        double orderCost = 0;
        boolean first = true;
        String orderID = Long.toString(System.currentTimeMillis());

        try {
            ResultSet resultSet = connection.prepareStatement("select * from cart where username = " + username).executeQuery();

            while (resultSet.next()){
                if(first){
                    orderProduct = resultSet.getString(3);
                    first =false;
                }
                else
                    orderProduct += "+" + resultSet.getString(3);

                orderCost += resultSet.getDouble(4) * resultSet.getInt(5);
            }

            OrderInfo orderInfo = new OrderInfo();
            orderInfo.setID(orderID);
            orderInfo.setProduct(orderProduct);
            orderInfo.setCost(orderCost);
            orderInfo.setStatus("待付款");

            if (orderCost!=0)
                connection.prepareStatement("insert orders values(" +
                        username +","+ orderInfo.getID() +","+ "\""+orderInfo.getProduct() +"\""+","+ orderInfo.getCost() +","+ "\""+ orderInfo.getStatus() +"\""
                                +")" ).executeUpdate();

            connection.prepareStatement("delete from cart where username = " + "\"" +username +"\"").executeUpdate();

        }catch(Exception e) {
            e.printStackTrace();
        }
    }
    //支付成功
    public static void SuccessPay(String orderID){
        setConnection();
        try {
            connection.prepareStatement("update orders set orderStatus = '已付款' where orderID = "+ "\""+ orderID+ "\"").executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
//宠物信息获取
    //获取所有宠物信息
    public static List<PetInfo> getAllPetInfo(){
        setConnection();
        List<PetInfo> petInfo = new ArrayList<PetInfo>();
        try {
            ResultSet pet =  connection.prepareStatement("select * from pet").executeQuery();
            while (pet.next()) {
                PetInfo p = new PetInfo();
                p.setPetClass(pet.getString(1));
                p.setPetName(pet.getString(2));
                p.setCost(pet.getDouble(3));
                p.setDescription(pet.getString(4));
                p.setPid(pet.getString(6));
                petInfo.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return petInfo;
    }

    //获取某Category(Class)的宠物信息
    public static List<PetInfo> getCategoryPetInfo(String category){
        setConnection();
        List<PetInfo> petInfo = new ArrayList<PetInfo>();
        try {
            ResultSet pet =  connection.prepareStatement("select * from pet where class = " + "\""+ category +"\"" ).executeQuery();
            while (pet.next()) {
                PetInfo p = new PetInfo();
                p.setPetClass(pet.getString(1));
                p.setPetName(pet.getString(2));
                p.setCost(pet.getDouble(3));
                p.setDescription(pet.getString(4));
                p.setPid(pet.getString(6));
                petInfo.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return petInfo;
    }

    //获取搜索的宠物信息
    public static List<PetInfo> getSearchResult(String keyword){
        setConnection();
        List<PetInfo> petInfo = new ArrayList<PetInfo>();
        try {
            ResultSet pet =  connection.prepareStatement("select * from pet").executeQuery();
            while (pet.next()){
                if(pet.getString("keyword").toUpperCase().contains(keyword.toUpperCase())) {
                    PetInfo p = new PetInfo();
                    p.setPetClass(pet.getString(1));
                    p.setPetName(pet.getString(2));
                    p.setCost(pet.getDouble(3));
                    p.setDescription(pet.getString(4));
                    p.setPid(pet.getString(6));
                    petInfo.add(p);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return petInfo;
    }

    //判断搜索有无结果
    public static boolean getSearched(String keyword){
        setConnection();
        List<PetInfo> petInfo = new ArrayList<PetInfo>();
        try {
            ResultSet pet =  connection.prepareStatement("select * from pet").executeQuery();
            while (pet.next()){
                if(pet.getString("keyword").contains(keyword)) {
                    return true;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    //获取特定宠物信息(通过pid)
    public static PetInfo getPetInfo(String pid){
        PetInfo p = new PetInfo();
        try {
            if(connection.isClosed() == true) {
                setConnection();
            }
            ResultSet pet =  connection.prepareStatement("select * from pet where pid = " +"\""+  pid +"\"").executeQuery();
            if(pet.next()){
                    p.setPetClass(pet.getString(1));
                    p.setPetName(pet.getString(2));
                    p.setCost(pet.getDouble(3));
                    p.setDescription(pet.getString(4));
                    p.setPid(pet.getString(6));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return p;
    }

//日志登记
    public static void setLogInfo(String username,String Servlet){
         try {
             setConnection();
             //获取当前时间
             SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
             java.util.Date date = new Date(System.currentTimeMillis());
             String time = formatter.format(date);
             String LogInfo = Servlet;
             connection.prepareStatement("INSERT history values("+"\""+ username +"\","+"\""+LogInfo+"\","+"\""+time+"\""+")").executeUpdate();
         } catch (SQLException e) {
             e.printStackTrace();
         }
    }
}
